import pymysql
from dbutils.pooled_db import PooledDB

from copy_data_from_csv_to_mongo import copy_ticket_from_csv_to_mongo
from my_models import client, Datas, delete_database

# pip install dbutils pymysql

pool = PooledDB(
    creator=pymysql,  # 使用 PyMySQL 作为连接库
    maxconnections=50,  # 连接池中允许的最大连接数
    mincached=2,  # 启动时创建的空闲连接数量
    maxcached=5,  # 连接池中允许的最大空闲连接数
    blocking=True,  # 达到最大连接数后，阻塞而不是抛出错误
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    db="BloodPressure",
)


def copy_data_from_mysql_to_mongo():
    """
    将Mysql数据导入到Mongo
    """
    conn = pool.connection()
    cursor = conn.cursor()
    # 根据需求修改SQL语句
    cursor.execute("SELECT time, h, m, sbp, dbp, device_id FROM bp3")
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    for row in data:
        # 此处额外进行了数据验证，MongoDB实际上可以直接插入dict数据
        blood_pressure = Datas(
            time=row[0],
            h=row[1],
            m=row[2],
            sbp=row[3],
            dbp=row[4],
            device_id=row[5],
        )
        client.blood_pressure.datas.insert_one(blood_pressure.model_dump())


if __name__ == "__main__":
    delete_database()  # 清空MongoDB
    copy_data_from_mysql_to_mongo()  # 从Mysql导入数据
    copy_ticket_from_csv_to_mongo("tickets.csv")  # 从csv导入设备信息

# def copy_data_from_csv_to_mysql(file_path):
#     """
#     将csv数据导入到Mysql
#     """
#     from pandas import read_csv
#     data = read_csv(file_path)
#     conn = pool.connection()
#     cursor = conn.cursor()
#     # 清空bp3
#     sql = "DELETE FROM bp3"
#     cursor.execute(sql)
#     conn.commit()
#     for _, row in data.iterrows():
#         blood_pressure = Datas(
#             time=row["time"],
#             h=row["h"],
#             m=row["m"],
#             sbp=row["sbp"],
#             dbp=row["dbp"],
#             device_id=row["device_id"],
#         )
#         sql = (f"INSERT INTO bp3 "
#                f"(time, h, m, sbp, dbp, device_id) "
#                f"VALUES ("
#                f"{blood_pressure.time}, "
#                f"{blood_pressure.h}, "
#                f"blood_pressure.m}, "
#                f"{blood_pressure.sbp}, "
#                f"{blood_pressure.dbp}, '"
#                f"{blood_pressure.device_id}')")
#         try:
#             cursor.execute(sql)
#             conn.commit()
#         except:
#             print(blood_pressure.model_dump())
#             pass
#     cursor.close()
#     conn.close()
